
USE [ppjdb]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[svc].[uspGetStoreIPList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [svc].[uspGetStoreIPList]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
  -----------------------------------------------------------------------
  File Name		:
  Description	:	Returns list of records from tblStoreIPList table base 
	on value of @biStoreID parameter
  Author        :   Sergey Morozov
  Copyright     :   (c) 2012 PPJ
  Incept		:	08/14/2012
  -----------------------------------------------------------------------
  Description/Purpose:

  Output values:
	@iRetCode =  0, sucess,
	@iRetCode = -1, SQL update error, detail info recorded into dbo.tblSQLAudit table
	@iRetCode = -2, Parameter @biStoreID is NULL or zero
	@iRetCode = -3, Store {0} not found in dbo.tblStore table
	@iRetCode = -4, Store {0} has "revoked" status

  -----------------------------------------------------------------------
  Change Log:
	Author			Date         Change
  -----------------------------------------------------------------------
  Sergiy Morozov 08/14/2012		development
  
  -----------------------------------------------------------------------
  Sample Script
  -----------------------------------------------------------------------

	declare @biStoreID [bigint]
		,@iPageIndex [int]
		,@iPageSize [int]    
		,@szSortColumn [varchar] (50)
		,@szSortDirection [varchar] (50)
		,@biResultCount [bigint]
		,@iRetCode [int]
		,@szMsgTier1 [nvarchar] (2048)
		,@szMsgTier2 [nvarchar] (1024)

	select @biStoreID = 1
		,@iPageIndex = 1
		,@iPageSize = 10
		,@szSortColumn = 'DomainName'
		,@szSortDirection = 'ASC'

	exec [svc].[uspGetStoreIPList]
		@biStoreID
		,@iPageIndex
		,@iPageSize
		,@szSortColumn
		,@szSortDirection
		,@biResultCount output
		,@iRetCode output
		,@szMsgTier1 output   
		,@szMsgTier2 output  

	select 	
		@iRetCode as [RetCode] 
		,@szMsgTier1 as [RetMsg1]   
		,@szMsgTier2 as [RetMsg2]   		

*/

CREATE PROCEDURE [svc].[uspGetStoreIPList]
	@biStoreID [bigint]
	,@iPageIndex [int]
	,@iPageSize [int]    
	,@szSortColumn [varchar] (50) = null
	,@szSortDirection [varchar] (50) = null
	,@biResultCount [bigint] output
    ,@iRetCode [int] output
    ,@szMsgTier1 [nvarchar] (2048) output   
    ,@szMsgTier2 [nvarchar] (1024) output  
AS
BEGIN

SET NOCOUNT ON;

DECLARE @iIsProcessComplete int
, @szProcessName VARCHAR(50)
, @ErrorMessage nvarchar(2048) 
, @ErrorNumber int
, @ErrorSeverity int
, @ErrorState int
, @ErrorProcedure nvarchar(2048)
, @ErrorLine int
, @TranCounter int
, @RowCount int
, @Msg nvarchar(4000)
, @iJobSetID int
, @szInsertedBy varchar(16)
, @szErrSubst [varchar] (100)

-- Get current stored proc name
SELECT @szProcessName = ISNULL(OBJECT_NAME(@@PROCID), 'uspGetStoreIPList')

BEGIN TRY
-----------------------------------
--BEGIN WORK
-----------------------------------

select @iRetCode = 0
	,@szMsgTier1 =''
	,@szMsgTier2=''
	,@szErrSubst = ''

declare @tbl table (StoreIPListID [bigint] not null,
	StoreID [bigint] not null,
	IPAddress [varchar] (64) not null,
	DomainName [nvarchar] (128) not null,
	TimerValue1 [int] not null,
	TimerValue2 [int] not null,
	RevokeStatus [bit] not null,
	DateRevoked [datetime] null,
	DateCreated [datetime] null,
	DateUpdated [datetime] null,
	SortColumn [nvarchar] (512) null,
	[RowNum] [bigint] not null primary key)	

declare @bTmpStoreStatus [bit]

-- Validate parameters
if coalesce(@biStoreID,0)=0
-- Parameter is NULL or zero
	set @iRetCode = -2
else 	
 begin
	select @bTmpStoreStatus = RevokeStatus from dbo.tblStore (nolock) where StoreID = @biStoreID
	
	if @@ROWCOUNT = 0 
		select @iRetCode = -3
			,@szErrSubst = CAST(@biStoreID as [varchar] (30))
	else if coalesce(@bTmpStoreStatus,0) = 1
		select @iRetCode = -4
			,@szErrSubst = CAST(@biStoreID as [varchar] (30))
 end

if @iRetCode = 0
 begin

	insert into @tbl (
		StoreIPListID,
		StoreID,
		IPAddress,
		DomainName,
		TimerValue1,
		TimerValue2,
		RevokeStatus,
		DateRevoked,
		DateCreated,
		DateUpdated,
		[RowNum])
	select 
		StoreIPListID,
		StoreID,
		IPAddress,
		DomainName,
		TimerValue1,
		TimerValue2,
		RevokeStatus,
		DateRevoked,
		DateCreated,
		DateUpdated,
		StoreIPListID as RowNum
		from dbo.tblStoreIPList (nolock) 
		where StoreID = @biStoreID
		order by StoreIPListID

	select @biResultCount = @@ROWCOUNT

	if coalesce(@szSortColumn,'')<>'' and coalesce(@szSortDirection,'')<>''
	 begin
		update @tbl set [SortColumn] =
		case 
			when @szSortColumn = 'IPAddress' then IPAddress
			when @szSortColumn = 'DomainName' then DomainName
			when @szSortColumn = 'TimerValue1' then cast([TimerValue1] as [varchar] (10))
			when @szSortColumn = 'TimerValue2' then cast([TimerValue2] as [varchar] (10))
			when @szSortColumn = 'DateCreated' then convert([varchar] (30), [DateCreated],120)
			when @szSortColumn = 'DateUpdated' then convert([varchar] (30), [DateUpdated],120)			
			when @szSortColumn = 'DateRevoked' then convert([varchar] (30), [DateRevoked],120)			
			when @szSortColumn = 'RevokeStatus' then cast([RevokeStatus] as [varchar] (10))
			else cast(StoreIPListID as [varchar] (30))					
		end

		if @szSortDirection = 'ASC'
		 begin
			;with t0 as (
				select [StoreIPListID],[RowNum],ROW_NUMBER() OVER(ORDER BY SortColumn) as RNumber from @tbl
			)
			update t set [RowNum] = t0.RNumber
			from @tbl t
			inner join t0 on t0.[StoreIPListID] = t.[StoreIPListID] and t0.[RowNum]=t.[RowNum]
		 end
		else
		 begin

			;with t0 as (
				select [StoreIPListID],[RowNum],ROW_NUMBER() OVER(ORDER BY SortColumn desc) as RNumber from @tbl
			)
			update t set [RowNum] = t0.RNumber
			from @tbl t
			inner join t0 on t0.[StoreIPListID] = t.[StoreIPListID] and t0.[RowNum]=t.[RowNum]
		 end
	 end

	select 	StoreIPListID,
		IPAddress,
		DomainName,
		TimerValue1,
		TimerValue2,
		RevokeStatus,
		DateRevoked,
		DateCreated,
		DateUpdated,
		[RowNum]
	from @tbl
	WHERE RowNum between ((@iPageIndex - 1) * @iPageSize + 1) and @iPageIndex*@iPageSize   

 end
else
 begin
	select @szMsgTier1=replace(t.MsgTier1,'{0}',''''+@szErrSubst+''''),
		@szMsgTier2=replace(u.MsgTier2,'{0}',''''+@szErrSubst+''''),
		@iRetCode= case when u.[UIErrCode] is null then @iRetCode else t.[UIErrCode] end
		from [dbo].[tblMsgOutput] t (nolock)
		left join [dbo].[tblUIMessage] u (nolock) on u.[UIErrCode] = t.[UIErrCode]
		where t.SPName = @szProcessName
		and t.RetCode = @iRetCode
 end

 RETURN 0
END TRY
BEGIN CATCH
-- If failed rollback
-- get extended error information
	select @ErrorNumber  = ERROR_NUMBER(), @ErrorSeverity  = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()
	, @ErrorProcedure  = ERROR_PROCEDURE(), @ErrorLine  = ERROR_LINE(), @ErrorMessage  = ERROR_MESSAGE()
	set @Msg = '; ErrorNumber = ' + cast(@ErrorNumber as varchar(4))
	+ ', ErrorSeverity  = '       + cast(@ErrorSeverity as varchar(4))
	+ ', ErrorState = '                 + cast(@ErrorState as varchar(4))
	+ ', ErrorProcedure = '       + cast(@ErrorProcedure as varchar(128))
	+ ', ErrorLine = '                  + cast(@ErrorLine as varchar(4))
	+ ', ErrorMessage = '         + cast(@ErrorMessage as varchar(2048))

	IF @TranCounter is not null
	 begin
		IF @TranCounter = 0 -- Transaction started in procedure.
		 ROLLBACK TRANSACTION;
		ELSE IF XACT_STATE() <> -1 -- roll back to the savepoint
		 ROLLBACK TRANSACTION uspGetStoreIPList;
	 end

	-- log error
	EXECUTE  dbo.uspRecordSQLAudit
	@biProcessID = -1
	,@iErrorNumber = @ErrorNumber
	,@iErrorSeverity = @ErrorSeverity
	,@iErrorState = @ErrorState
	,@szErrorProcedure = @ErrorProcedure
	,@iErrorLine = @ErrorLine
	,@szErrorMessage = @ErrorMessage
	,@szParameters = ''
	,@iInserted = 0

	select @iRetCode = -1 

	select @szMsgTier1=@ErrorMessage,
		@szMsgTier2=u.MsgTier2,
		@iRetCode= case when u.[UIErrCode] is null then @iRetCode else t.[UIErrCode] end
		from [dbo].[tblMsgOutput] t (nolock)
		left join [dbo].[tblUIMessage] u (nolock) on u.[UIErrCode] = t.[UIErrCode]
		where t.SPName = @szProcessName
		and t.RetCode = @iRetCode

RETURN -1
END CATCH

END
GO

if not exists (select 1 from dbo.tblMsgOutput (nolock) where SPName = 'uspGetStoreIPList')
insert into dbo.tblMsgOutput (SPName,RetCode,MsgTier1,UIErrCode)
select 'uspGetStoreIPList',-1, 'SQL update error',207
union all
select 'uspGetStoreIPList',-2, 'Parameter @biStoreID is NULL or zero',207
union all
select 'uspGetStoreIPList',-3, 'Store {0} not found in dbo.tblStore table',207
union all
select 'uspGetStoreIPList',-4, 'StoreID {0} has "revoked" status',214
go